package com.monkeyk.os.infrastructure.jdbc;

import com.monkeyk.os.domain.oauth.AccessToken;
import com.monkeyk.os.domain.users.Roles;
import com.monkeyk.os.domain.users.Users;
import com.monkeyk.os.domain.users.UsersAuthzRepository;
import com.monkeyk.os.service.dto.UsersFormDto;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

/**
 * 2016/6/3
 * <p>
 * 账号(Users) 相关的 JDBC操作实现
 *
 * @author Shengzhao Li
 */
@Repository("usersJdbcAuthzRepository")
public class UsersJdbcAuthzRepository extends AbstractJdbcRepository implements UsersAuthzRepository {

    private static UsersRowMapper usersRowMapper = new UsersRowMapper();

    private static RolesRowMapper rolesRowMapper = new RolesRowMapper();

    private static UserStateRowMapper userStateRowMapper = new UserStateRowMapper();

    private static AccessTokenRowMapper accessTokenRowMapper = new AccessTokenRowMapper();

    @Override
    public List<Users> findUsersByUsername(String username) {
        String sql = " select u.* from hp_owner u where u.archived = 0 ";

        if (StringUtils.isNotEmpty(username)) {
            sql += " and u.username = ?  order by u.id desc ";
            return this.jdbcTemplate.query(sql, usersRowMapper, username);
        }
        sql += " order by u.id desc ";
        return this.jdbcTemplate.query(sql, usersRowMapper);
    }

    public AccessToken queryByTokenId(String token_id) {
        String sql = "SELECT * FROM hp_oauth_access_token WHERE token_id = ?";
        final List<AccessToken> list = this.jdbcTemplate.query(sql, accessTokenRowMapper, token_id);
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public List<Roles> findAvailableRolesList() {
        String sql = " select r.* from hp_roles r where r.archived = 0 ";
        return this.jdbcTemplate.query(sql, rolesRowMapper);
    }

    @Override
    public Users findByUsername(String username) {
        String sql = " select * from hp_owner where phone = ? ";
        final List<Users> list = this.jdbcTemplate.query(sql, usersRowMapper, username);
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public Users findUserState(String id) {
        String sql = " select owner_state,owner_id from hp_owner_room where owner_id = ? and default_room = '1' ";
        final List<Users> list = this.jdbcTemplate.query(sql, userStateRowMapper, id);
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public String saveUsers(final Users users) {
        String sql = " insert into hp_owner(id,guid,create_date, phone,password,archived,username,deviceid) values (?,?,?,?,?,?,?,?) ";
        this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, users.guid());
                ps.setString(2, users.guid());
                ps.setTimestamp(3, new Timestamp(users.createTime().getTime()));
                ps.setString(4, users.phone());
                ps.setString(5, users.password());
                ps.setString(6, "0");
                ps.setString(7, users.phone());
                ps.setString(8, users.deviceid());
            }
        });

        return this.jdbcTemplate.queryForObject("select id from hp_owner where guid = ?", new Object[] { users.guid() }, String.class);
    }

    @Override
    public Roles findRolesByGuid(String guid) {
        final List<Roles> list = this.jdbcTemplate.query(" select * from hp_roles where guid = ?", rolesRowMapper, guid);
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public void insertUserRoles(final String userId, final int rolesId) {
        String sql = "insert into hp_user_roles(users_id,roles_id) values (?,?) ";
        this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, userId);
                ps.setInt(2, rolesId);
            }
        });
    }

    @Override
    public List<Roles> findUsersRolesList(String usersGuid) {
        String sql = " select r.* from hp_roles r where r.archived = 0 and r.id in (" +
                " select roles_id from hp_user_roles where users_id = (" +
                " select id from hp_owner where guid = ? and archived = 0))";
        return this.jdbcTemplate.query(sql, rolesRowMapper, usersGuid);
    }

    @Override
    public List<String> findPermissionsByRoles(String rolesGuid) {
        String sql = " select p.permission from hp_roles_permissions p where p.roles_id = (" +
                " select id from hp_roles where guid = ? and archived = 0 )";
        return this.jdbcTemplate.queryForList(sql, new Object[] { rolesGuid }, String.class);
    }

    /**
     * 退出登录，切换用户状态
     *
     * @param user
     */
    @Override
    public void updateUserLogin(final String user, final String state) {
        String sql = "update hp_owner SET islogin = ? ,update_date = ?,deviceid='' WHERE username = ?";
        this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, state);
                ps.setTimestamp(2, new Timestamp(new Date().getTime()));
                ps.setString(3, user);

            }
        });
    }

    /**
     * 退出登录，切换用户状态
     *
     * @param
     */
    @Override
    public void updatePhone(final String guid, final String phone) {
        String sql = "update hp_owner SET phone = ? ,username = ? WHERE guid = ?";
        this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, phone);
                ps.setString(2, phone);
                ps.setString(3, guid);

            }
        });
    }

    @Override
    public void updateAccessPhone(final String tokenId, final String username) {
        String sql = "update hp_oauth_access_token SET username = ? WHERE token_id = ?";
        this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, username);
                ps.setString(2, tokenId);
            }
        });
    }

    @Override
    public void updateUser(final Users user) {
        String sql = "update hp_owner SET deviceid = ? WHERE guid = ?";
        this.jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {

                ps.setString(1, user.deviceid());
                ps.setString(2, user.guid());
            }
        });
    }

}
